package com.pennphoto.server;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import org.apache.james.mime4j.field.datetime.DateTime;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import com.pennphoto.client.QueryXML;
import com.pennphoto.shared.PennPhotoConstants;


/**
 * This class operates on the server (not the browser) and does a computation.
 * Its output will be returned to the browser.
 * 
 * @author zives
 *
 */
public class QueryXMLImpl extends RemoteServiceServlet implements QueryXML {
	

	@Override
	public Integer getFirstPerson(String xml) throws IllegalArgumentException {
	 
		return Integer.parseInt(xml);
	}
	
	// Change this query.  Use %PERSON where you want to substitute the person's name
	
	@Override
	public String getPersonAndNeighbors(final int person)
			throws IllegalArgumentException,Exception {
		String user="user";
		Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
		String circle="";
		ArrayList<Integer> friendID = new ArrayList<Integer>();
		ArrayList<String> temp = new ArrayList<String>();
		ArrayList<ArrayList<String>> friends = new ArrayList<ArrayList<String>> ();
		String ret = "<"+"?"+"xml version='"+"1.0"+"' encoding='"+"UTF-8"+"'?"+">";
		try
		{
			Statement stmt = conn.createStatement();
			try{
				ResultSet rs = stmt.executeQuery("select circlename,firstname,lastname,friendID from "+PennPhotoConstants.schemaName+"users,"+PennPhotoConstants.schemaName+"circle where users.userID=circle.friendID and circle.ownerID="+(person));
				try{
					while(rs.next())
					{	
						if(!rs.getString(1).equals(circle)){
							if (!temp.isEmpty()){
								friends.add(temp);
							}
							temp=new ArrayList<String>();
							circle= new String(rs.getString(1));
							temp.add(circle);
							temp.add(rs.getString(2).toString()+ " " +rs.getString(3));
							friendID.add(rs.getInt(4));
						}
						else {
							temp.add(rs.getString(2).toString()+ " " +rs.getString(3));
							friendID.add(rs.getInt(4));
						}
					}
					friends.add(temp);
				}finally{
					try{rs.close();} catch(Exception ignore){}
				}		
			}finally{
				try{stmt.close();} catch(Exception ignore){}
			}
		Statement stmt4 = conn.createStatement();
		ResultSet rs4 = stmt4.executeQuery("select studid from "+PennPhotoConstants.schemaName+"student where studid="+person);
		
		Statement stmt5 = conn.createStatement();
		ResultSet rs5 = stmt5.executeQuery("select profid from "+PennPhotoConstants.schemaName+"professor where profid="+person);
		
		if (rs4.next()){
			ret+="<student><id>"+person+"</id>";
			user="student";
		}
		else if (rs5.next()){
			ret+="<professor><id>"+person+"</id>";
			user="professor";
		}
		else
			ret+="<user><id>"+person+"</id>";
		Statement stmt1= conn.createStatement();
		ResultSet rs2 = stmt1.executeQuery("select firstname,lastname,email,dob,gender from "+PennPhotoConstants.schemaName+"users where userID="+person);
		while (rs2.next()){
			int age = rs2.getDate(4).getYear();
			age=111-age;
			ret+="<name>"+rs2.getString(1)+ " " + rs2.getString(2)+"</name><email>"+rs2.getString(3)+"</email><age>"+ age +"</age><gender>"+rs2.getString(5)+"</gender>";
		}
		ret+="<photo>";
		Statement stmt2= conn.createStatement();
		ResultSet rs3 = stmt1.executeQuery("select photoid,URL from "+PennPhotoConstants.schemaName+"photos where uploadedby="+person);
		while (rs3.next()){
			ret+="<photoID>"+rs3.getInt(1)+"</photoID>"+"<url>"+rs3.getURL(2)+"</url>";
		}
		int k=0;
		ret+="</photo>";
		ret+="<circle>";
		for (int i = 0; i<friends.size();i++)
		{ ret+="<name>"+friends.get(i).get(0)+"</name>";
			for(int j=1;j<friends.get(i).size();j++){
				temp=friends.get(i);
				ret+="<containsFriend><id>"+friendID.get(k++)+"</id><name>"+temp.get(j)+"</name></containsFriend>";
			}
		}
		ret+="</circle>";
		if (user.equals("student")){
			Statement stmt6 = conn.createStatement();
			ResultSet rs6= stmt6.executeQuery("select advisedby from "+PennPhotoConstants.schemaName+"student where studid=" + person);
			while (rs6.next()){
				ret+="<advisedby>"+rs6.getString(1)+"</advisedby>";
			}
			ret+="</student>";
		}
		else if (user.equals("professor")){
			Statement stmt7 = conn.createStatement();
			ResultSet rs7= stmt7.executeQuery("select studid from "+PennPhotoConstants.schemaName+"advises where profID=" + person);
			while (rs7.next()){
				ret+="<advises>"+rs7.getString(1)+"</advises>";
			}
			ret+="</professor>";
		}
		else
			ret+="</user>";
		// Escape data from the client to avoid cross-site script vulnerabilities.
		}finally {
			try{conn.close();} catch(Exception ignore){}
		}
		
		System.out.println(ret);
		return ret;
	}

	// Change this query.  Use %PERSON where you want to substitute the person's name.
	// But it should only return the requested number of photos. 
	
	@Override
	public String getPhotos(final int person, int first, int count)
			throws IllegalArgumentException,Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
		String ret="";
		ret+="<result>";
		Statement stmt2= conn.createStatement();
		ResultSet rs3 = stmt2.executeQuery("select * from ( select a.*, ROWNUM rnum from ( select photoid,URL from "+PennPhotoConstants.schemaName+"photos where uploadedby="+person+") a where ROWNUM <= "+first+count +")where rnum  >="+ first);
		while (rs3.next()){
			ret+="<photo><photoID>"+rs3.getInt(1)+"</photoID>"+"<url>"+rs3.getURL(2)+"</url></photo>";
		}
		ret+="</result>";
		return ret;
		
	}
	
	@Override
	public ArrayList<Integer> getFriends(final int person) throws IllegalArgumentException, Exception
	{
		ArrayList<Integer> friends = new ArrayList<Integer>();
		Class.forName("oracle.jdbc.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("Select friendid from "+PennPhotoConstants.schemaName+"friends where userid="+person);
		while(rs.next()){
			friends.add(rs.getInt(1));
		}
		return friends;
	}
	
	
}
